package com.fpno.preparedStatement.crud;

import com.fpno.util.JDBCUtils;
import org.junit.Test;

import java.io.InputStream;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Properties;

/*
* 增删改，查
* */
public class PreparedStatementUpdateTest {
    @Test
    public void testCommonUpdate() throws Exception {
//        String sql="delete from customers where id=?";
//        update(sql,3);
        /*order是关键字，若他为表名则会查询出错 需要加上单引号 或者用数据库.表名的格式*/
        String sql="update test.order set order_name= ? where order_id=  ?";
        update(sql,"DD",2);
    }

    //通用的增删改操作
    public void update(String sql,Object ...args) throws Exception {//可变形参的个数就是填充占位符的个数
        //获取数据库的链接
        Connection connection = JDBCUtils.getConnection();
        //预编译sql语句
        PreparedStatement ps = connection.prepareStatement(sql);
        //填充占位符
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i+1,args[i]);
        }
        //执行
        ps.execute();
        //关闭资源
        JDBCUtils.closeResource(connection,ps);
    }








    //修改customer表的一个记录
    @Test
    public void testUpdate() throws Exception {
        //获取连接
        Connection connection = JDBCUtils.getConnection();
        //预编译sql语句，返回preparedstatement的实例
        String sql="update customers set name=? where id=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        //填充占位符
        ps.setObject(1,"莫扎特");
        ps.setObject(2,"18");
        //执行
        ps.execute();
        //关闭资源
        JDBCUtils.closeResource(connection,ps);
    }







    //向customers中添加一项纪录
    @Test
    public void testInsert() {
        Connection connection=null;
        PreparedStatement ps=null;
        try {
            //获取连接
            InputStream resource = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
            Properties properties = new Properties();
            properties.load(resource);
            String user = properties.getProperty("user");
            String password = properties.getProperty("password");
            String url = properties.getProperty("url");
            String driverClass = properties.getProperty("driverClass");
            Class.forName(driverClass);
            connection = DriverManager.getConnection(url, user, password);
            //System.out.println(connection);

            //预编译sql语句，返回prepareStatement的实例,问号代表占位符
            String sql="insert into customers(name ,email,birth)values (?,?,?)";
            ps = connection.prepareStatement(sql);
            //填充占位符
            ps.setString(1,"哪吒");
            ps.setString(2,"nezha@mail.com");
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            java.util.Date date = sdf.parse("1000-01-01");
            ps.setDate(3,new Date(date.getTime()));

            //执行sql
            ps.execute();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //资源关闭
            try {
                if (ps!=null){
                    ps.close();
                }

            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                if (connection!=null){
                    connection.close();
                }

            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }



    }
}
